<?php

function connect_db($user, $password, $dbname, $hostname="localhost") {
  if (!mysql_connect($hostname, $user, $password)) {
    die("connect to DB failed");
  }

  if (!mysql_select_db($dbname)) {
    die("mysql_select_db failed");
  }

  if (!mysql_query("set names utf8")) {
    die("failed to set names utf8");
  }
}

function sql_or_die($sql) {
  $ret = mysql_query($sql);
  if (!$ret) {
    die("sql( $sql ) failed:" . mysql_error() . "<br />");
  } else {
    return $ret;
  }
}

function bookmark_exists($url) {
  $sql = sprintf("select count(url) as count from bookmark where url = '%s'", $url);
  $ret = mysql_query($sql);
  $count = mysql_fetch_assoc($ret);
  return $count['count'] > 0;
}

function add_bookmark($url, $title, $text, $user) {
  $sql = sprintf("insert into bookmark(url, title, text, user) values('%s', '%s', '%s', '%s')",
                 $url, $title, $text, $user);
  $ret = mysql_query($sql);
  return $ret;
}

function remove_bookmark($url) {
  $sql = sprintf("delete from bookmark where url = '%s'", $url);
  $ret = mysql_query($sql);
  return $ret;
}

function update_bookmark_desc($id, $text) {
  $sql = sprintf("update bookmark set text = '%s' where id = %d", $text, $id);
  $ret = mysql_query($sql);
  return $ret;
}

function get_bookmark_by_id($id) {
  $sql = "select * from bookmark where id = $id";
  $ret = mysql_query($sql);
  if (!$ret) {
    return false;
  } else {
    return mysql_fetch_assoc($ret);
  }
}

function get_rate_info($bookmark_id, $user) {
  $sql = "select rate from rate_history where username = '$user' and bookmark_id = '$bookmark_id'";
  $ret = sql_or_die($sql);
  $row = mysql_fetch_row($ret);
  return $row[0];
}

function set_rate_info($bookmark_id, $value, $user) {
  $sql = "replace into rate_history (username, bookmark_id, rate) values('$user', '$bookmark_id', $value)";
  return sql_or_die($sql);
}

function update_bookmark_rate($id, $value, $user) {
  if ($value > 0)
    $value = 1;
  if ($value < 0)
    $value = -1;
  $old_rate = get_rate_info($id, $user);
  if ($old_rate == $value) {
    return false;
  }
  $sql = '';
  if ($value > 0) {
    $sql = sprintf("update bookmark set up = up + %d where id = %d", $value, $id);
  } else if ($value < 0) {
    $sql = sprintf("update bookmark set down = down + %d where id = %d", -$value, $id);
  }

  if ($sql === '') {
    return true;
  }

  sql_or_die($sql);

  return set_rate_info($id, $value, $user);
}

function invitation_code_exists($code) {
  $sql = "select count(*) as count from invitation_code where code = '$code'";
  $ret = sql_or_die($sql);
  $result = mysql_fetch_assoc($ret);
  return $result['count'] == 1;
}

function add_invitation_code($code) {
  $sql = "insert into invitation_code(code, user) values('$code', null)";
  $ret = sql_or_die($sql);
  echo mysql_affected_rows() . " rows affected \n";
}

function invitation_code_usable($code) {
  if (!invitation_code_exists($code))
    return false;
  $sql = "select user from invitation_code where code = '$code'";
  $ret = sql_or_die($sql);
  $user = mysql_fetch_row($ret);
  return $user[0] == null;
}

function email_used($email) {
  $sql = "select email from user where email = '$email'";
  $ret = sql_or_die($sql);
  return mysql_num_rows($ret) > 0;
}

function username_used($username) {
  $sql = "select username from user where username = '$username'";
  $ret = sql_or_die($sql);
  return mysql_num_rows($ret) > 0;
}

function use_register_code($code, $user) {
  $sql = "update invitation_code set user = '$user' where code = '$code'";
  sql_or_die($sql);
}

function register($code, $username, $password, $email) {
  if (strlen($username) < 3 || strlen($password) < 6) {
    return 3;
  }
  if (username_used($username)) {
    return 1;
  } else if (email_used($email)) {
    return 2;
  }
  $password_md5 = md5($password);
  $sql = "insert into user(username, password, email) values('$username', '{$password_md5}', '$email')";
  $ret = sql_or_die($sql);
  use_register_code($code, $username);
  return 0;
}

function login($username, $password) {
  $sql = "select username from user where username = '$username' and password = '$password'";
  $ret = sql_or_die($sql);
  return mysql_num_rows($ret) == 1;
}

function create_session($username, $timeout) {
  $session = md5($username . time());
  $sql = "insert into user_session(username, session, timeout) values ('$username', '$session', '$timeout')";
  $ret = sql_or_die($sql);
  return $session;
}

function remove_session($session) {
  $sql = "delete from user_session where session = '$session'";
  sql_or_die($sql);
}

function get_user_by_session($session) {
  $sql = "select * from user_session where session = '$session'";
  $ret = sql_or_die($sql);
  if (mysql_num_rows($ret) == 0)
    return null;
  $now = date("Y-m-d H:i:s");
  $row = mysql_fetch_assoc($ret);
  if ($now > $row['timeout']) {
    // session timed out
    remove_session($session);
    return null;
  }
  return $row['username'];
}
?>